﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Dry.DbInterpreter;
using System.IO;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Dry.Generators
{
    public class DALGenerator : GeneratorBase
    {
        private static string _entLibVersion;
        private static bool _generateDryLog;
        private static string _tablePrefix;
        private static bool _generateInnerJoinCode;

        public static GeneratorFeedback CreateFile(string strTableName, string strEntLibVersion, bool generateDryLog)
        {
            return CreateFile(strTableName, strEntLibVersion, generateDryLog, "tbl", false);
        }

        public static GeneratorFeedback CreateFile(string strTableName, string strEntLibVersion, bool generateDryLog, string tablePrefix, bool generateInnerJoinCode)
        {
            GeneratorFeedback generatorFeedback = new GeneratorFeedback();

            _entLibVersion = strEntLibVersion;
            _generateDryLog = generateDryLog;
            _tablePrefix = tablePrefix;
            _generateInnerJoinCode = generateInnerJoinCode;

            string strCode = GetDALClass(strTableName);
            generatorFeedback.Exception = CreateFileFromString("DAL\\AutoGenerated\\" + strTableName.Substring(tablePrefix.Length, strTableName.Length - tablePrefix.Length) + "DAL.cs", strCode);
            generatorFeedback.Lines = CountLinesInString(strCode);

            return generatorFeedback;
        }

        public static void CreateBaseDAL()
        {
            string strCode = Templates.BaseDAL.GetBaseDAL(_entLibVersion).Replace("¤ProjectName¤", Dry.ProjectName);
            CreateFileFromString("DAL\\BaseDAL.cs", strCode);   
        }

        public static string GetDALClass(string strTableName)
        {
            string strCode = String.Empty;
            DbTable dbTable = new DbTable(strTableName, _tablePrefix);

            strCode += GetFileHeader(Dry.ProjectName, _entLibVersion, _generateDryLog.ToString(), "", _tablePrefix, _generateInnerJoinCode.ToString());

            strCode += @"using System;" + _strNewLine;
            strCode += @"using System.Collections.Generic;" + _strNewLine;
            strCode += @"using System.Data;" + _strNewLine;
            strCode += @"using System.Data.Common;" + _strNewLine;
            strCode += @"using System.Data.SqlClient;" + _strNewLine;
            strCode += @"using System.Linq;" + _strNewLine;
            strCode += @"using Microsoft.Practices.EnterpriseLibrary.Data;" + _strNewLine;
            strCode += @"using " + Dry.ProjectName + ".BO;" + _strNewLine;
            strCode += @"using " + Dry.ProjectName + ".IP;" + _strNewLine;
            strCode += @"using " + Dry.ProjectName + ".BLL;" + _strNewLine;
            strCode += @"" + _strNewLine;
            strCode += @"namespace " + Dry.ProjectName + ".DAL" + _strNewLine;
            strCode += @"{" + _strNewLine;
            strCode += @"    public partial class " + dbTable.ClassName + "DAL : BaseDAL" + _strNewLine;
            strCode += @"    {" + _strNewLine;
            
            AddGetByIdMethod(ref strCode, dbTable);
            strCode += @"" + _strNewLine;

            AddGetSqlFromIP(ref strCode, dbTable);
            strCode += @"" + _strNewLine;

            AddGetSqlFromBO(ref strCode, dbTable);
            strCode += @"" + _strNewLine;

            if (_generateInnerJoinCode)
            {
                AddGetSqlFromListOfJoins(ref strCode, dbTable);
                strCode += @"" + _strNewLine;
            }

            AddGetSqlFromListOfIPs(ref strCode, dbTable, _generateInnerJoinCode);
            strCode += @"" + _strNewLine;

            AddAddInParameters(ref strCode, dbTable);
            strCode += @"" + _strNewLine;

            AddGetListByIPMethod(ref strCode, dbTable);
            strCode += @"" + _strNewLine;

            AddGetIdListByIPMethod(ref strCode, dbTable);
            strCode += @"" + _strNewLine;

            AddCountByIPMethod(ref strCode, dbTable);
            strCode += @"" + _strNewLine;

            AddGetBOFromDataRecord(ref strCode, dbTable);
            strCode += @"" + _strNewLine;
            
            if(!strTableName.EndsWith("Enum"))
            {
                AddUpdateMethod(ref strCode, dbTable);
                strCode += @"" + _strNewLine;
                AddInsertMethod(ref strCode, dbTable);
                strCode += @"" + _strNewLine;
                AddInsertWithIdentityMethod(ref strCode, dbTable);
                strCode += @"" + _strNewLine;
                AddDeleteMethod(ref strCode, dbTable);
            }
            strCode += @"    }" + _strNewLine;
            strCode += @"}";

            return strCode;
        }

        protected static DataTable GetRelationTable(string strTableName, bool distinct)
        {
            // Get table relations
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand("exec sp_fkeys @pktable_name = '" + strTableName + "'");

            string[] columnNameFk = new[] { "PKTABLE_NAME", "FKCOLUMN_NAME", "PKCOLUMN_NAME" };
            string[] columnNamePk = new[] { "FKTABLE_NAME", "FKCOLUMN_NAME", "PKCOLUMN_NAME" };
            string[] columnNameFinal = new[] { "TABLE", "COLUMN", "PKCOLUMN" };

            DataTable dataTablePk = db.ExecuteDataSet(dbCommand).Tables[0].DefaultView.ToTable(distinct, columnNamePk);
            dataTablePk.Columns[0].ColumnName = "TABLE";
            dataTablePk.Columns[1].ColumnName = "COLUMN";
            dataTablePk.Columns[2].ColumnName = "PKCOLUMN";

            dbCommand = db.GetSqlStringCommand("exec sp_fkeys @fktable_name = '" + strTableName + "'");

            DataTable dataTableFk = db.ExecuteDataSet(dbCommand).Tables[0].DefaultView.ToTable(distinct, columnNameFk);
            dataTableFk.Columns[0].ColumnName = "TABLE";
            dataTableFk.Columns[1].ColumnName = "COLUMN";
            dataTableFk.Columns[2].ColumnName = "PKCOLUMN";

            dataTableFk.Merge(dataTablePk);
            DataTable dataTableFinal = dataTableFk.DefaultView.ToTable(true, columnNameFinal);

            return dataTableFinal;
        }

        protected static string GetEnumNameFromRelationTable(DataRow dataRow, string strTableName)
        {

            string strTable = (strTableName.TrimStart(_tablePrefix.ToCharArray()) + "Id");
            string strRelationTable = dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray());
            string strRelationColumn = dataRow["COLUMN"].ToString();
            string strReurn = strRelationTable + "_" + (strRelationColumn.Contains(strTable) ? strRelationColumn.Substring(0, strRelationColumn.IndexOf(strTable)) : strRelationColumn.Contains("Id") ? strRelationColumn.Substring(0, strRelationColumn.IndexOf("Id")) : strRelationColumn);

            if (strReurn.EndsWith("_"))
                strReurn = strReurn.Substring(0, strReurn.Length - 1);

            return strReurn;
        }

        protected static void AddGetByIdMethod(ref string strCode, DbTable dbTable)
        {
            strCode += "        public static " + dbTable.ClassName + "BO GetById(int int" + dbTable.ClassName + "Id)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            Database db = GetDatabase();" + _strNewLine;
            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(\"SELECT * FROM " + dbTable.TableName + " WHERE " + dbTable.ClassName + "Id = @" + dbTable.ClassName + "Id\");" + _strNewLine;
            strCode += "            db.AddInParameter(dbCommand, \"" + dbTable.ClassName + "Id\", DbType.Int32, int" + dbTable.ClassName + "Id);" + _strNewLine;
            if (_entLibVersion == "5")
            {
                strCode += "            using (RefCountingDataReader refCountingDataReader = ((RefCountingDataReader)db.ExecuteReader(dbCommand)))" + _strNewLine;
            }
            else
            {
                strCode += "            using (SqlDataReader dataReader = (SqlDataReader)db.ExecuteReader(dbCommand))" + _strNewLine;
            }
            strCode += "            {" + _strNewLine;
            if (_entLibVersion == "5")
                strCode += "                SqlDataReader dataReader = (SqlDataReader)refCountingDataReader.InnerReader;" + _strNewLine;
            strCode += "                if(dataReader.Read())" + _strNewLine;
            strCode += "                    return GetBOFromDataRecord(dataReader);" + _strNewLine;
            strCode += "                else" + _strNewLine;
            strCode += "                    throw new Exception(\"Dry could not find " + dbTable.ClassName + "BO with Id: \" + int" + dbTable.ClassName + "Id.ToString());" + _strNewLine;
            strCode += "            }" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddGetSqlFromIP(ref string strCode, DbTable dbTable)
        {
            strCode += "        public static string GetSqlFromIP(" + dbTable.ClassName + "IP " + dbTable.ObjectName + "IP, string strInputParameterSuffix)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            return GetSqlFromIP(" + dbTable.ObjectName + "IP, strInputParameterSuffix, \"" + dbTable.TableName + "\");" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;

            strCode += "        public static string GetSqlFromIP(" + dbTable.ClassName + "IP " + dbTable.ObjectName + "IP, string strInputParameterSuffix, string strInputTableName)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            string strSql = \"(\";" + _strNewLine;

            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                // Add property =
                strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + " != null)" + _strNewLine;
                strCode += "                strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " = @" + dbColumn.ColumnName + "\" + strInputParameterSuffix + \" AND \";" + _strNewLine;
                strCode += " " + _strNewLine;

                // Add extra for datetime columns
                if (GetCodeTypeFromDbType(dbColumn.DataType) == "DateTime")
                {
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date != null)" + _strNewLine;
                    strCode += "                strSql += \"dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) = @" + dbColumn.ColumnName + "_Date\" + strInputParameterSuffix + \" AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time != null)" + _strNewLine;
                    strCode += "                strSql += \"dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) = @" + dbColumn.ColumnName + "_Time\" + strInputParameterSuffix + \" AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;
                }

                // Add property LIKE '%input%', 'input%' and '%input'
                if (dbColumn.ShouldHaveLikeParameters())
                {
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like != null)" + _strNewLine;
                    strCode += "                strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " LIKE @" + dbColumn.ColumnName + "_Like\" + strInputParameterSuffix + \" AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like_Start != null)" + _strNewLine;
                    strCode += "                strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " LIKE @" + dbColumn.ColumnName + "_Like_Start\" + strInputParameterSuffix + \" AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like_End != null)" + _strNewLine;
                    strCode += "                strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " LIKE @" + dbColumn.ColumnName + "_Like_End\" + strInputParameterSuffix + \" AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Not != null)" + _strNewLine;
                    strCode += "                strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " <> @" + dbColumn.ColumnName + "_Not\" + strInputParameterSuffix + \" AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;
                }

                // Add prpoperty IN
                if (dbColumn.ShouldHaveInParameters())
                {
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_In != null)" + _strNewLine;
                    strCode += "            {" + _strNewLine;
                    strCode += "                if (" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_In.Count == 0)" + _strNewLine;
                    strCode += "                    strSql += \"1=0 AND \";" + _strNewLine;
                    strCode += "                else" + _strNewLine;
                    strCode += "                    strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " IN (\" + GetStringFromList(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_In) + \") AND \";" + _strNewLine;
                    strCode += "            }" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_NotIn != null && " + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_NotIn.Count > 0)" + _strNewLine;
                    strCode += "                strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " NOT IN (\" + GetStringFromList(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_NotIn) + \") AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;
                }

                // Add property <= and =>
                if (dbColumn.ShouldHaveFromAndToParameters())
                {
                    // Obsolete
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_From != null)" + _strNewLine;
                    strCode += "                strSql += \"@" + dbColumn.ColumnName + "_From\" + strInputParameterSuffix + \" <= \" + strInputTableName + \"." + dbColumn.ColumnName + " AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_To != null)" + _strNewLine;
                    strCode += "                strSql += \"@" + dbColumn.ColumnName + "_To\" + strInputParameterSuffix + \" >= \" + strInputTableName + \"." + dbColumn.ColumnName + " AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    // Replaces old
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_LesserOrEqualThan != null)" + _strNewLine;
                    strCode += "                strSql += \"@" + dbColumn.ColumnName + "_LesserOrEqualThan\" + strInputParameterSuffix + \" >= \" + strInputTableName + \"." + dbColumn.ColumnName + " AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_GreaterOrEqualThan != null)" + _strNewLine;
                    strCode += "                strSql += \"@" + dbColumn.ColumnName + "_GreaterOrEqualThan\" + strInputParameterSuffix + \" <= \" + strInputTableName + \"." + dbColumn.ColumnName + " AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_LesserThan != null)" + _strNewLine;
                    strCode += "                strSql += \"@" + dbColumn.ColumnName + "_LesserThan\" + strInputParameterSuffix + \" > \" + strInputTableName + \"." + dbColumn.ColumnName + " AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_GreaterThan != null)" + _strNewLine;
                    strCode += "                strSql += \"@" + dbColumn.ColumnName + "_GreaterThan\" + strInputParameterSuffix + \" < \" + strInputTableName + \"." + dbColumn.ColumnName + " AND \";" + _strNewLine;
                    strCode += " " + _strNewLine;

                    // Add extra for datetime columns
                    if (GetCodeTypeFromDbType(dbColumn.DataType) == "DateTime")
                    {
                        // Obsolete
                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_From != null)" + _strNewLine;
                        strCode += "                strSql += \"@" + dbColumn.ColumnName + "_Date_From\" + strInputParameterSuffix + \" <= dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_To != null)" + _strNewLine;
                        strCode += "                strSql += \"@" + dbColumn.ColumnName + "_Date_To\" + strInputParameterSuffix + \" >= dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_From != null)" + _strNewLine;
                        strCode += "                strSql += \"dateadd(day, -datediff(day, 0, @" + dbColumn.ColumnName + "_Time_From\" + strInputParameterSuffix + \"), @" + dbColumn.ColumnName + "_Time_From\" + strInputParameterSuffix + \") <= dateadd(day, -datediff(day, 0,\" + strInputTableName + \"." + dbColumn.ColumnName + "),\" + strInputTableName + \"." + dbColumn.ColumnName + ") AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_To != null)" + _strNewLine;
                        strCode += "                strSql += \"dateadd(day, -datediff(day, 0, @" + dbColumn.ColumnName + "_Time_To\" + strInputParameterSuffix + \"), @" + dbColumn.ColumnName + "_Time_To\" + strInputParameterSuffix + \") >= dateadd(day, -datediff(day, 0,\" + strInputTableName + \"." + dbColumn.ColumnName + "),\" + strInputTableName + \"." + dbColumn.ColumnName + ") AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        // Replaces old
                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_LesserOrEqualThan != null)" + _strNewLine;
                        strCode += "                strSql += \"@" + dbColumn.ColumnName + "_Date_LesserOrEqualThan\" + strInputParameterSuffix + \" >= dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_GreaterOrEqualThan != null)" + _strNewLine;
                        strCode += "                strSql += \"@" + dbColumn.ColumnName + "_Date_GreaterOrEqualThan\" + strInputParameterSuffix + \" <= dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_LesserThan != null)" + _strNewLine;
                        strCode += "                strSql += \"@" + dbColumn.ColumnName + "_Date_LesserThan\" + strInputParameterSuffix + \" > dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_GreaterThan != null)" + _strNewLine;
                        strCode += "                strSql += \"@" + dbColumn.ColumnName + "_Date_GreaterThan\" + strInputParameterSuffix + \" < dateadd(dd, -datediff(dd, \" + strInputTableName + \"." + dbColumn.ColumnName + ", 1), 1) AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_LesserOrEqualThan != null)" + _strNewLine;
                        strCode += "                strSql += \"dateadd(day, -datediff(day, 0, @" + dbColumn.ColumnName + "_Time_LesserOrEqualThan\" + strInputParameterSuffix + \"), @" + dbColumn.ColumnName + "_Time_From\" + strInputParameterSuffix + \") >= dateadd(day, -datediff(day, 0,\" + strInputTableName + \"." + dbColumn.ColumnName + "),\" + strInputTableName + \"." + dbColumn.ColumnName + ") AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_GreaterOrEqualThan != null)" + _strNewLine;
                        strCode += "                strSql += \"dateadd(day, -datediff(day, 0, @" + dbColumn.ColumnName + "_Time_GreaterOrEqualThan\" + strInputParameterSuffix + \"), @" + dbColumn.ColumnName + "_Time_To\" + strInputParameterSuffix + \") <= dateadd(day, -datediff(day, 0,\" + strInputTableName + \"." + dbColumn.ColumnName + "),\" + strInputTableName + \"." + dbColumn.ColumnName + ") AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_LesserThan != null)" + _strNewLine;
                        strCode += "                strSql += \"dateadd(day, -datediff(day, 0, @" + dbColumn.ColumnName + "_Time_LesserThan\" + strInputParameterSuffix + \"), @" + dbColumn.ColumnName + "_Time_From\" + strInputParameterSuffix + \") > dateadd(day, -datediff(day, 0,\" + strInputTableName + \"." + dbColumn.ColumnName + "),\" + strInputTableName + \"." + dbColumn.ColumnName + ") AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_GreaterThan != null)" + _strNewLine;
                        strCode += "                strSql += \"dateadd(day, -datediff(day, 0, @" + dbColumn.ColumnName + "_Time_GreaterThan\" + strInputParameterSuffix + \"), @" + dbColumn.ColumnName + "_Time_To\" + strInputParameterSuffix + \") < dateadd(day, -datediff(day, 0,\" + strInputTableName + \"." + dbColumn.ColumnName + "),\" + strInputTableName + \"." + dbColumn.ColumnName + ") AND \";" + _strNewLine;
                        strCode += " " + _strNewLine;
                    }
                }

                // Add property ISNULL
                if (dbColumn.IsNullable)
                {
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_IsNull.HasValue)" + _strNewLine;
                    strCode += "            {" + _strNewLine;
                    strCode += "                strSql += \"\" + strInputTableName + \"." + dbColumn.ColumnName + " IS \";" + _strNewLine;
                    strCode += "                if(!" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_IsNull.Value)" + _strNewLine;
                    strCode += "                    strSql += \"NOT \";" + _strNewLine;
                    strCode += "                strSql += \"NULL AND \";" + _strNewLine;                    
                    strCode += "            }" + _strNewLine;
                    strCode += " " + _strNewLine;
                }
            }
            strCode += "            strSql = strSql.TrimEnd(\"AND \".ToCharArray());" + _strNewLine;
            strCode += "            strSql += \")\";" + _strNewLine;
            strCode += "            return strSql;" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddGetSqlFromBO(ref string strCode, DbTable dbTable)
        {
            strCode += "        public static string GetSqlFromBO(bool includeTableName)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            return GetSqlFromBO(includeTableName, \"" + dbTable.TableName + "\");" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;

            strCode += "        public static string GetSqlFromBO(bool includeTableName, string tableName)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            string strSql = \"\";" + _strNewLine;
            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                strCode += "            strSql += includeTableName ? tableName + \"." + dbColumn.ColumnName + " AS \" + tableName.TrimStart(\"" + _tablePrefix + "\".ToCharArray()) + \"_" + dbColumn.ColumnName + ", \" : tableName + \"." + dbColumn.ColumnName + ", \";" + _strNewLine;
            }
            strCode += "            if (strSql.EndsWith(\", \"))"+ _strNewLine;
            strCode += "                strSql = strSql.Substring(0, strSql.Length - 2);"+ _strNewLine;
            strCode += "            return strSql;"+ _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddGetSqlFromListOfJoins(ref string strCode, DbTable dbTable)
        {
            Database db = GetDatabase();
            DbCommand dbCommand;

            int intFk = 0;
            dbCommand = db.GetSqlStringCommand("exec sp_fkeys @pktable_name = '" + dbTable.TableName + "'");
            intFk = intFk + db.ExecuteDataSet(dbCommand).Tables[0].Rows.Count;

            dbCommand = db.GetSqlStringCommand("exec sp_fkeys @fktable_name = '" + dbTable.TableName + "'");
            intFk = intFk + db.ExecuteDataSet(dbCommand).Tables[0].Rows.Count;

            if (intFk > 0)
            {
                strCode += "        public static string GetSqlFromListOfJoins( List<Object> lstJoin)" + _strNewLine;
                strCode += "        {" + _strNewLine;
                strCode += "            string strSql = \"\";" + _strNewLine;

                strCode += "            foreach (Object join in lstJoin)" + _strNewLine;
                strCode += "            {" + _strNewLine;
                strCode += "                switch (join.GetType().ToString())" + _strNewLine;
                strCode += "                {" + _strNewLine;
                strCode += "                    case \"Backend.BLL." + dbTable.ClassName + "BLL+InnerJoinEnum\":" + _strNewLine;
                strCode += "                        switch ((" + dbTable.ClassName + "BLL.InnerJoinEnum) join)" + _strNewLine;
                strCode += "                        {" + _strNewLine;

                foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, false).Rows)
                {
                    strCode += "                            case " + dbTable.ClassName + "BLL.InnerJoinEnum." + GetEnumNameFromRelationTable(dataRow, dbTable.TableName) + ":" + _strNewLine;
                    strCode += "                                strSql += \" INNER JOIN " + _tablePrefix + "\" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(0, join.ToString().IndexOf('_')) : join) + \" AS \" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(join.ToString().IndexOf('_') + 1) : join) + \" ON \" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(join.ToString().IndexOf('_') + 1) : join) + \"." + dataRow["PKCOLUMN"] + " = " + dbTable.TableName + "." + dataRow["COLUMN"] + " \";" + _strNewLine;
                    strCode += "                            break;" + _strNewLine;
                }

                strCode += "                        }" + _strNewLine;
                strCode += "                    break;" + _strNewLine;

                strCode += "                    case \"Backend.BLL." + dbTable.ClassName + "BLL+LeftJoinEnum\":" + _strNewLine;
                strCode += "                        switch ((" + dbTable.ClassName + "BLL.LeftJoinEnum) join)" + _strNewLine;
                strCode += "                        {" + _strNewLine;

                foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, false).Rows)
                {
                    strCode += "                            case " + dbTable.ClassName + "BLL.LeftJoinEnum." + GetEnumNameFromRelationTable(dataRow, dbTable.TableName) + ":" + _strNewLine;
                    strCode += "                                strSql += \" LEFT JOIN " + _tablePrefix + "\" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(0 ,join.ToString().IndexOf('_')) : join) + \" AS \" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(join.ToString().IndexOf('_') + 1) : join) + \" ON \" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(join.ToString().IndexOf('_') + 1) : join) + \"." + dataRow["PKCOLUMN"] + " = " + dbTable.TableName + "." + dataRow["COLUMN"] + " \";" + _strNewLine;
                    strCode += "                            break;" + _strNewLine;
                }

                strCode += "                        }" + _strNewLine;
                strCode += "                    break;" + _strNewLine;

                strCode += "                    case \"Backend.BLL." + dbTable.ClassName + "BLL+RightJoinEnum\":" + _strNewLine;
                strCode += "                        switch ((" + dbTable.ClassName + "BLL.RightJoinEnum) join)" + _strNewLine;
                strCode += "                        {" + _strNewLine;

                foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, false).Rows)
                {
                    strCode += "                            case " + dbTable.ClassName + "BLL.RightJoinEnum." + GetEnumNameFromRelationTable(dataRow, dbTable.TableName) + ":" + _strNewLine;
                    strCode += "                                strSql += \" RIGHT JOIN " + _tablePrefix + "\" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(0 ,join.ToString().IndexOf('_')) : join) + \" AS \" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(join.ToString().IndexOf('_') + 1) : join) + \" ON \" + (join.ToString().Contains(\"_\") ? join.ToString().Substring(join.ToString().IndexOf('_') + 1) : join) + \"." + dataRow["PKCOLUMN"] + " = " + dbTable.TableName + "." + dataRow["COLUMN"] + " \";" + _strNewLine;
                    strCode += "                            break;" + _strNewLine;
                }

                strCode += "                        }" + _strNewLine;
                strCode += "                    break;" + _strNewLine;

                strCode += "                    default:" + _strNewLine;
                strCode += "                        throw new Exception(\"Join type not valid  for this BO.\");" + _strNewLine;
                strCode += "                 }" + _strNewLine;
                strCode += "            }" + _strNewLine;

                strCode += "            return strSql;" + _strNewLine;
                strCode += "        }" + _strNewLine;
                strCode += "   " + _strNewLine;
            }
        }

        protected static void AddGetSqlFromListOfIPs(ref string strCode, DbTable dbTable, bool blnGenerateInnerJoinCode)
        {
            strCode += "        public static string GetSqlFromListOfIPs(List<" + dbTable.ClassName + "IP> lst" + dbTable.ClassName + "IP, List<SortOrderType> lstOrderBy)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            string strSql = string.Empty;" + _strNewLine;
            strCode += "            for (int i = 0; i < lst" + dbTable.ClassName + "IP.Count; i++)" + _strNewLine;
            strCode += "            {" + _strNewLine;
            strCode += "                strSql += GetSqlFromIP(lst" + dbTable.ClassName + "IP[i], \"_\" + i.ToString());" + _strNewLine;
            strCode += "                if (strSql.EndsWith(\"()\"))" + _strNewLine;
            strCode += "                    strSql = strSql.TrimEnd(\"()\".ToCharArray());" + _strNewLine;
            strCode += "                else" + _strNewLine;
            strCode += "                    strSql += \"OR \";" + _strNewLine;
            strCode += "            }" + _strNewLine;

            strCode += "            if (strSql.EndsWith(\"OR \"))" + _strNewLine;
            strCode += "                strSql = strSql.Substring(0, strSql.Length - 3);" + _strNewLine;


            strCode += "            if (strSql == \"\")" + _strNewLine;
            strCode += "                strSql = \"1=1\";" + _strNewLine;

            strCode += "            if(lstOrderBy.Count > 0)" + _strNewLine;
            strCode += "            {" + _strNewLine;
            strCode += "                strSql += \" ORDER BY \";" + _strNewLine;
            strCode += "                foreach (SortOrderType sortOrder in lstOrderBy)" + _strNewLine;
            strCode += "                    strSql += TranslateSortType2Sql(sortOrder.ToString()) + \", \";" + _strNewLine;
            strCode += "                strSql = strSql.Substring(0, strSql.Length - 2);" + _strNewLine;
            strCode += "            }" + _strNewLine;

            strCode += "            return strSql;" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;


            if (blnGenerateInnerJoinCode)
            {
                Database db = GetDatabase();
                DbCommand dbCommand;

                int intFk = 0;
                dbCommand = db.GetSqlStringCommand("exec sp_fkeys @pktable_name = '" + dbTable.TableName + "'");
                intFk = intFk + db.ExecuteDataSet(dbCommand).Tables[0].Rows.Count;

                dbCommand = db.GetSqlStringCommand("exec sp_fkeys @fktable_name = '" + dbTable.TableName + "'");
                intFk = intFk + db.ExecuteDataSet(dbCommand).Tables[0].Rows.Count;

                if (intFk > 0)
                {
                    strCode += "        public static string GetSqlFromListOfIPs(List<" + dbTable.ClassName + "IP> lst" + dbTable.ClassName + "IP, Dictionary<Object, Object> lstInnerJoinWithIP, List<String> lstOrderBy)" + _strNewLine;
                    strCode += "        {" + _strNewLine;
                    strCode += "            string strSql = string.Empty;" + _strNewLine;
                    strCode += "            int i = -1;" + _strNewLine;

                    strCode += "            foreach (KeyValuePair<Object, Object> keyValuePair in lstInnerJoinWithIP)" + _strNewLine;
                    strCode += "            {" + _strNewLine;
                    strCode += "                i++;" + _strNewLine;
                    strCode += "                if (keyValuePair.Value != null)" + _strNewLine;
                    strCode += "                {" + _strNewLine;
                    strCode += "                    string strInputTableName = \"\";" + _strNewLine;
                    strCode += "                    switch (keyValuePair.Key.GetType().ToString())" + _strNewLine;
                    strCode += "                    {" + _strNewLine;
                    strCode += "                        case \"Backend.BLL." + dbTable.ClassName + "BLL+InnerJoinEnum\":" + _strNewLine;
                    strCode += "                            strInputTableName = (((" + dbTable.ClassName + "BLL.InnerJoinEnum) keyValuePair.Key).ToString().Contains(\"_\") ? ((" + dbTable.ClassName + "BLL.InnerJoinEnum) keyValuePair.Key).ToString().Substring(((" + dbTable.ClassName + "BLL.InnerJoinEnum) keyValuePair.Key).ToString().IndexOf('_') + 1) : ((" + dbTable.ClassName + "BLL.InnerJoinEnum) keyValuePair.Key).ToString());" + _strNewLine;
                    strCode += "                        break;" + _strNewLine;
                    strCode += "                        case \"Backend.BLL." + dbTable.ClassName + "BLL+LeftJoinEnum\":" + _strNewLine;
                    strCode += "                            strInputTableName = (((" + dbTable.ClassName + "BLL.LeftJoinEnum) keyValuePair.Key).ToString().Contains(\"_\") ? ((" + dbTable.ClassName + "BLL.LeftJoinEnum) keyValuePair.Key).ToString().Substring(((" + dbTable.ClassName + "BLL.LeftJoinEnum) keyValuePair.Key).ToString().IndexOf('_') + 1) : ((" + dbTable.ClassName + "BLL.LeftJoinEnum) keyValuePair.Key).ToString());" + _strNewLine;
                    strCode += "                        break;" + _strNewLine;
                    strCode += "                        case \"Backend.BLL." + dbTable.ClassName + "BLL+RightJoinEnum\":" + _strNewLine;
                    strCode += "                            strInputTableName = (((" + dbTable.ClassName + "BLL.RightJoinEnum) keyValuePair.Key).ToString().Contains(\"_\") ? ((" + dbTable.ClassName + "BLL.RightJoinEnum) keyValuePair.Key).ToString().Substring(((" + dbTable.ClassName + "BLL.RightJoinEnum) keyValuePair.Key).ToString().IndexOf('_') + 1) : ((" + dbTable.ClassName + "BLL.RightJoinEnum) keyValuePair.Key).ToString());" + _strNewLine;
                    strCode += "                        break;" + _strNewLine;
                    strCode += "                    }" + _strNewLine;
                    strCode += "                    switch (keyValuePair.Value.ToString())" + _strNewLine;
                    strCode += "                    {" + _strNewLine;

                    foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, true).DefaultView.ToTable(true, "TABLE").Rows)
                    {
                        strCode += "                      case \"Backend.IP." + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "IP\":" + _strNewLine;
                        strCode += "                         strSql += " + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "DAL.GetSqlFromIP((" + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "IP)keyValuePair.Value, \"_\" + i.ToString(), strInputTableName);" + _strNewLine;
                        strCode += "                            if (strSql.EndsWith(\"()\"))" + _strNewLine;
                        strCode += "                                strSql = strSql.TrimEnd(\"()\".ToCharArray());" + _strNewLine;
                        strCode += "                            else" + _strNewLine;
                        strCode += "                                strSql += \"AND \";" + _strNewLine;
                        strCode += "                        break;" + _strNewLine;
                    }

                    strCode += "                        default:" + _strNewLine;
                    strCode += "                            throw new Exception(\"IP not valid  for this BO.\");" + _strNewLine;
                    strCode += "                    }" + _strNewLine;
                    strCode += "                }" + _strNewLine;
                    strCode += "            }" + _strNewLine;

                    strCode += "            foreach (" + dbTable.ClassName + "IP " + dbTable.ClassName + "Ip in lst" + dbTable.ClassName + "IP)" + _strNewLine;
                    strCode += "            {" + _strNewLine;
                    strCode += "                i++;" + _strNewLine;
                    strCode += "                strSql += GetSqlFromIP(" + dbTable.ClassName + "Ip, \"_\" + i.ToString());" + _strNewLine;
                    strCode += "                if (strSql.EndsWith(\"()\"))" + _strNewLine;
                    strCode += "                    strSql = strSql.TrimEnd(\"()\".ToCharArray());" + _strNewLine;
                    strCode += "                else" + _strNewLine;
                    strCode += "                    strSql += \"OR \";" + _strNewLine;
                    strCode += "            }" + _strNewLine;
                    strCode += "            if (strSql.EndsWith(\"OR \"))" + _strNewLine;
                    strCode += "                strSql = strSql.Substring(0, strSql.Length - 3);" + _strNewLine;

                    strCode += "            if (strSql.EndsWith(\"AND \"))" + _strNewLine;
                    strCode += "                strSql = strSql.Substring(0, strSql.Length - 4);" + _strNewLine;
                    strCode += "            if (strSql == \"\")" + _strNewLine;
                    strCode += "                strSql = \"1=1\";" + _strNewLine;
                    strCode += "            if (lstOrderBy.Count > 0)" + _strNewLine;
                    strCode += "            {" + _strNewLine;
                    strCode += "                strSql += \" ORDER BY \";" + _strNewLine;
                    strCode += "                foreach (String sortOrder in lstOrderBy)" + _strNewLine;
                    strCode += "                {" + _strNewLine;
                    strCode += "                    if (!String.IsNullOrEmpty(sortOrder))" + _strNewLine;
                    strCode += "                        strSql += TranslateSortType2Sql(sortOrder) + \", \";" + _strNewLine;
                    strCode += "                }" + _strNewLine;
                    strCode += "                if (strSql.EndsWith(\", \"))" + _strNewLine;
                    strCode += "                    strSql = strSql.Substring(0, strSql.Length - 2);" + _strNewLine;

                    strCode += "                if (strSql.EndsWith(\"ORDER BY \"))" + _strNewLine;
                    strCode += "                    strSql = strSql.Substring(0, strSql.Length - 9);" + _strNewLine;
                    strCode += "            }" + _strNewLine;
                    strCode += "            return strSql;" + _strNewLine;
                    strCode += "        }" + _strNewLine;
                    strCode += "   " + _strNewLine;
                }
            }
        }

        protected static void AddCountByIPMethod(ref string strCode, DbTable dbTable)
        {
            strCode += "        public static Int32 CountByIP(List<" + dbTable.ClassName + "IP> lst" + dbTable.ClassName + "IP)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            Database db = GetDatabase();" + _strNewLine;

            strCode += "            string strSql = \"SELECT COUNT(*) FROM " + dbTable.TableName + " WHERE \";" + _strNewLine;

            strCode += "            strSql += GetSqlFromListOfIPs(lst" + dbTable.ClassName + "IP, new List<SortOrderType>());" + _strNewLine;

            strCode += "            if (strSql.EndsWith(\"WHERE \"))" + _strNewLine;
            strCode += "                strSql = strSql.Substring(0, strSql.Length - 6);" + _strNewLine;

            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(strSql);" + _strNewLine;

            strCode += "            for(int i = 0; i < lst" + dbTable.ClassName + "IP.Count; i++)" + _strNewLine;
            strCode += "            {" + _strNewLine;
            strCode += "                AddInParameters(lst" + dbTable.ClassName + "IP[i], db, dbCommand, \"_\" + i.ToString());" + _strNewLine;
            strCode += "            }" + _strNewLine;
            strCode += "" + _strNewLine;

            strCode += "            return int.Parse(db.ExecuteScalar(dbCommand).ToString());" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;   
        }

        protected static void AddGetIdListByIPMethod(ref string strCode, DbTable dbTable)
        {
            strCode += "        public static List<Int32> GetIdListByIP(List<" + dbTable.ClassName + "IP> lst" + dbTable.ClassName + "IP, List<SortOrderType> lstOrderBy)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            Database db = GetDatabase();" + _strNewLine;
            strCode += "            List<Int32> lst" + dbTable.ClassName + "Id = new List<Int32>();" + _strNewLine;

            strCode += "            string strSql = \"SELECT " + dbTable.ClassName + "Id FROM " + dbTable.TableName + " WHERE \";" + _strNewLine;

            strCode += "            strSql += GetSqlFromListOfIPs(lst" + dbTable.ClassName + "IP, lstOrderBy);" + _strNewLine;

            strCode += "            if (strSql.EndsWith(\"WHERE \"))" + _strNewLine;
            strCode += "                strSql = strSql.Substring(0, strSql.Length - 6);" + _strNewLine;

            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(strSql);" + _strNewLine;

            strCode += "            for(int i = 0; i < lst" + dbTable.ClassName + "IP.Count; i++)" + _strNewLine;
            strCode += "            {" + _strNewLine;
            strCode += "                AddInParameters(lst" + dbTable.ClassName + "IP[i], db, dbCommand, \"_\" + i.ToString());" + _strNewLine;
            strCode += "            }" + _strNewLine;
            strCode += "" + _strNewLine;

            if (_entLibVersion == "5")
            {
                strCode += "            using (RefCountingDataReader refCountingDataReader = ((RefCountingDataReader)db.ExecuteReader(dbCommand)))" + _strNewLine;
            }
            else
            {
                strCode += "            using (SqlDataReader dataReader = (SqlDataReader)db.ExecuteReader(dbCommand))" + _strNewLine;
            }


            strCode += "            {" + _strNewLine;

            if (_entLibVersion == "5")
                strCode += "                SqlDataReader dataReader = (SqlDataReader)refCountingDataReader.InnerReader;" + _strNewLine;
            strCode += "                if(dataReader.HasRows)" + _strNewLine;
            strCode += "                {" + _strNewLine;
            strCode += "                    while(dataReader.Read())" + _strNewLine;
            strCode += "                    {" + _strNewLine;
            strCode += "                        lst" + dbTable.ClassName + "Id.Add(dataReader.GetInt32(dataReader.GetOrdinal(\"" + dbTable.ClassName + "Id\")));" + _strNewLine;
            strCode += "                    }" + _strNewLine;
            strCode += "                }" + _strNewLine;
            strCode += "            }" + _strNewLine;
            strCode += "            return lst" + dbTable.ClassName + "Id;" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddGetListByIPMethod(ref string strCode, DbTable dbTable)
        {
            Database db = GetDatabase();
            DbCommand dbCommand;

            strCode += "        public enum SortOrderType" + _strNewLine;
            strCode += "        {" + _strNewLine;
            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                strCode += "            " + dbColumn.ColumnName + "_ASC," + _strNewLine;
                strCode += "            " + dbColumn.ColumnName + "_DESC," + _strNewLine;
            }
            strCode = strCode.Substring(0, strCode.Length - 3) + _strNewLine;
            strCode += "        }" + _strNewLine;

            strCode += "        public static List<" + dbTable.ClassName + "BO> GetListByIP(List<" + dbTable.ClassName + "IP> lst" + dbTable.ClassName + "IP, List<SortOrderType> lstOrderBy)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            Database db = GetDatabase();" + _strNewLine;
            strCode += "            List<" + dbTable.ClassName + "BO> lst" + dbTable.ClassName + " = new List<" + dbTable.ClassName + "BO>();" + _strNewLine;

            strCode += "            string strSql = \"SELECT * FROM " + dbTable.TableName + " WHERE \";" + _strNewLine;

            strCode += "            strSql += GetSqlFromListOfIPs(lst" +dbTable.ClassName + "IP, lstOrderBy);" + _strNewLine;

            strCode += "            if (strSql.EndsWith(\"WHERE \"))" + _strNewLine;
            strCode += "                strSql = strSql.Substring(0, strSql.Length - 6);" + _strNewLine;
            
            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(strSql);" + _strNewLine;

            strCode += "            for(int i = 0; i < lst" + dbTable.ClassName + "IP.Count; i++)" + _strNewLine;
            strCode += "            {" + _strNewLine;
            strCode += "                AddInParameters(lst" + dbTable.ClassName + "IP[i], db, dbCommand, \"_\" + i.ToString());" + _strNewLine;
            strCode += "            }" + _strNewLine;
            strCode += "" + _strNewLine;

            if (_entLibVersion == "5")
            {
                strCode += "            using (RefCountingDataReader refCountingDataReader = ((RefCountingDataReader)db.ExecuteReader(dbCommand)))" + _strNewLine;
            }
            else
            {
                strCode += "            using (SqlDataReader dataReader = (SqlDataReader)db.ExecuteReader(dbCommand))" + _strNewLine;
            }

            strCode += "            {" + _strNewLine;

            if (_entLibVersion == "5")
                strCode += "                SqlDataReader dataReader = (SqlDataReader)refCountingDataReader.InnerReader;" + _strNewLine;
            strCode += "                if(dataReader.HasRows)" + _strNewLine;
            strCode += "                {" + _strNewLine;
            strCode += "                    while(dataReader.Read())" + _strNewLine;
            strCode += "                    {" + _strNewLine;
            strCode += "                        lst" + dbTable.ClassName + ".Add(GetBOFromDataRecord(dataReader));" + _strNewLine;
            strCode += "                    }" + _strNewLine;
            strCode += "                }" + _strNewLine;
            strCode += "            }" + _strNewLine;
            strCode += "            return lst" + dbTable.ClassName + ";" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;

            // GetDataTable

            strCode += "        public static DataTable GetDataTableByIP(List<" + dbTable.ClassName + "IP> lst" + dbTable.ClassName + "IP, List<SortOrderType> lstOrderBy)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            Database db = GetDatabase();" + _strNewLine;
            strCode += "            List<" + dbTable.ClassName + "BO> lst" + dbTable.ClassName + " = new List<" + dbTable.ClassName + "BO>();" + _strNewLine;

            strCode += "            string strSql = \"SELECT * FROM " + dbTable.TableName + " WHERE \";" + _strNewLine;

            strCode += "            strSql += GetSqlFromListOfIPs(lst" + dbTable.ClassName + "IP, lstOrderBy);" + _strNewLine;

            strCode += "            if (strSql.EndsWith(\"WHERE \"))" + _strNewLine;
            strCode += "                strSql = strSql.Substring(0, strSql.Length - 6);" + _strNewLine;

            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(strSql);" + _strNewLine;

            strCode += "            for(int i = 0; i < lst" + dbTable.ClassName + "IP.Count; i++)" + _strNewLine;
            strCode += "            {" + _strNewLine;
            strCode += "                AddInParameters(lst" + dbTable.ClassName + "IP[i], db, dbCommand, \"_\" + i.ToString());" + _strNewLine;
            strCode += "            }" + _strNewLine;
            strCode += "" + _strNewLine;

            strCode += "            return db.ExecuteDataSet(dbCommand).Tables[0];" + _strNewLine;
            
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;



            // Overloaded GetListByIP Method
            if (_generateInnerJoinCode)
            {

                int intFk = 0;
                dbCommand = db.GetSqlStringCommand("exec sp_fkeys @pktable_name = '" + dbTable.TableName + "'");
                intFk = intFk + db.ExecuteDataSet(dbCommand).Tables[0].Rows.Count;

                dbCommand = db.GetSqlStringCommand("exec sp_fkeys @fktable_name = '" + dbTable.TableName + "'");
                intFk = intFk + db.ExecuteDataSet(dbCommand).Tables[0].Rows.Count;

                if (intFk > 0)
                {
                    strCode += "        public static DataTable GetListByIP(List<" + dbTable.ClassName + "IP> lst" + dbTable.ClassName + "IP, Dictionary<Object, Object> lstInnerJoinWithIP, List<String> lstOrderBy)" + _strNewLine;
                    strCode += "        {" + _strNewLine;
                    strCode += "            Database db = GetDatabase();" + _strNewLine;
                    strCode += "            string strSql = \"SELECT \" + GetSqlFromBO(true);" + _strNewLine;

                    strCode += "            foreach (KeyValuePair<Object, Object> keyValuePair in lstInnerJoinWithIP)" + _strNewLine;
                    strCode += "            {" + _strNewLine;
                    strCode += "                if (keyValuePair.Key.GetType().ToString() == \"Backend.BLL." + dbTable.ClassName + "BLL+InnerJoinEnum\")" + _strNewLine;
                    strCode += "                {" + _strNewLine;
                    strCode += "                    switch ((" + dbTable.ClassName + "BLL.InnerJoinEnum)keyValuePair.Key)" + _strNewLine;
                    strCode += "                    {" + _strNewLine;
                    foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, false).Rows)
                    {
                        strCode += "                        case " + dbTable.ClassName + "BLL.InnerJoinEnum." + GetEnumNameFromRelationTable(dataRow, dbTable.TableName) + ":" + _strNewLine;
                        strCode += "                            strSql += \", \" + " + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "DAL.GetSqlFromBO(true, (((" + dbTable.ClassName + "BLL.InnerJoinEnum)keyValuePair.Key).ToString().Contains(\"_\") ? ((" + dbTable.ClassName + "BLL.InnerJoinEnum)keyValuePair.Key).ToString().Substring(((" + dbTable.ClassName + "BLL.InnerJoinEnum)keyValuePair.Key).ToString().IndexOf('_') + 1) : ((" + dbTable.ClassName + "BLL.InnerJoinEnum)keyValuePair.Key).ToString()));" + _strNewLine;
                        strCode += "                        break;" + _strNewLine;
                    }
                    strCode += "                        default:" + _strNewLine;
                    strCode += "                            throw new Exception(\"Join type not valid  for this BO.\");" + _strNewLine;
                    strCode += "                    }" + _strNewLine;
                    strCode += "                }" + _strNewLine;
                    strCode += "                else if (keyValuePair.Key.GetType().ToString() == \"Backend.BLL." + dbTable.ClassName + "BLL+LeftJoinEnum\")" + _strNewLine;
                    strCode += "                {" + _strNewLine;
                    strCode += "                    switch ((" + dbTable.ClassName + "BLL.LeftJoinEnum)keyValuePair.Key)" + _strNewLine;
                    strCode += "                    {" + _strNewLine;
                    foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, false).Rows)
                    {
                        strCode += "                        case " + dbTable.ClassName + "BLL.LeftJoinEnum." + GetEnumNameFromRelationTable(dataRow, dbTable.TableName) + ":" + _strNewLine;
                        strCode += "                            strSql += \", \" + " + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "DAL.GetSqlFromBO(true, (((" + dbTable.ClassName + "BLL.LeftJoinEnum)keyValuePair.Key).ToString().Contains(\"_\") ? ((" + dbTable.ClassName + "BLL.LeftJoinEnum)keyValuePair.Key).ToString().Substring(((" + dbTable.ClassName + "BLL.LeftJoinEnum)keyValuePair.Key).ToString().IndexOf('_') + 1) : ((" + dbTable.ClassName + "BLL.LeftJoinEnum)keyValuePair.Key).ToString()));" + _strNewLine;
                        strCode += "                        break;" + _strNewLine;
                    }
                    strCode += "                        default:" + _strNewLine;
                    strCode += "                            throw new Exception(\"Join type not valid  for this BO.\");" + _strNewLine;
                    strCode += "                    }" + _strNewLine;
                    strCode += "                }" + _strNewLine;
                    strCode += "                else if (keyValuePair.Key.GetType().ToString() == \"Backend.BLL." + dbTable.ClassName + "BLL+RightJoinEnum\")" + _strNewLine;
                    strCode += "                {" + _strNewLine;
                    strCode += "                    switch ((" + dbTable.ClassName + "BLL.RightJoinEnum)keyValuePair.Key)" + _strNewLine;
                    strCode += "                    {" + _strNewLine;
                    foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, false).Rows)
                    {
                        strCode += "                        case " + dbTable.ClassName + "BLL.RightJoinEnum." + GetEnumNameFromRelationTable(dataRow, dbTable.TableName) + ":" + _strNewLine;
                        strCode += "                            strSql += \", \" + " + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "DAL.GetSqlFromBO(true, (((" + dbTable.ClassName + "BLL.RightJoinEnum)keyValuePair.Key).ToString().Contains(\"_\") ? ((" + dbTable.ClassName + "BLL.RightJoinEnum)keyValuePair.Key).ToString().Substring(((" + dbTable.ClassName + "BLL.RightJoinEnum)keyValuePair.Key).ToString().IndexOf('_') + 1) : ((" + dbTable.ClassName + "BLL.RightJoinEnum)keyValuePair.Key).ToString()));" + _strNewLine;
                        strCode += "                        break;" + _strNewLine;
                    }
                    strCode += "                        default:" + _strNewLine;
                    strCode += "                            throw new Exception(\"Join type not valid  for this BO.\");" + _strNewLine;
                    strCode += "                    }" + _strNewLine;
                    strCode += "                }" + _strNewLine;
                    strCode += "            }" + _strNewLine;

                    strCode += "            strSql += \" FROM " + dbTable.TableName + " \" + GetSqlFromListOfJoins(lstInnerJoinWithIP.Keys.ToList()) + \" WHERE \" + GetSqlFromListOfIPs(lst" + dbTable.ClassName + "IP, lstInnerJoinWithIP, lstOrderBy);" + _strNewLine;

                    strCode += "            if (strSql.EndsWith(\"WHERE \"))" + _strNewLine;
                    strCode += "                strSql = strSql.Substring(0, strSql.Length - 6);" + _strNewLine;
                    strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(strSql);" + _strNewLine;
                    strCode += "            List<Object> lstIPs = lstInnerJoinWithIP.Values.ToList();" + _strNewLine;

                    strCode += "            if (lstIPs.Count > 0) lstIPs.AddRange(lst" + dbTable.ClassName + "IP.Cast<Object>());" + _strNewLine;

                    strCode += "            for (int i = 0; i < lstIPs.Count; i++)" + _strNewLine;
                    strCode += "            {" + _strNewLine;

                    strCode += "                if (lstIPs[i] != null)" + _strNewLine;
                    strCode += "                {" + _strNewLine;

                    strCode += "                    switch (lstIPs[i].GetType().ToString())" + _strNewLine;
                    strCode += "                    {" + _strNewLine;
                    strCode += "                        case \"Backend.IP." + dbTable.ClassName + "IP\":" + _strNewLine;
                    strCode += "                            " + dbTable.ClassName + "DAL.AddInParameters((" + dbTable.ClassName + "IP)lstIPs[i], db, dbCommand, \"_\" + i.ToString());" + _strNewLine;
                    strCode += "                         break;" + _strNewLine;

                    foreach (DataRow dataRow in GetRelationTable(dbTable.TableName, true).DefaultView.ToTable(true, "TABLE").Rows)
                    {
                        if (dataRow["TABLE"].ToString() != dbTable.TableName)
                        {
                            strCode += "                        case \"Backend.IP." + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "IP\":" + _strNewLine;
                            strCode += "                            " + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "DAL.AddInParameters((" + dataRow["TABLE"].ToString().TrimStart(_tablePrefix.ToCharArray()) + "IP)lstIPs[i], db, dbCommand,\"_\" + i.ToString());" + _strNewLine;
                            strCode += "                            break;" + _strNewLine;
                        }
                    }

                    strCode += "                        default:" + _strNewLine;
                    strCode += "                            throw new Exception(\"IP not valid  for this BO.\");" + _strNewLine;
                    strCode += "                    }" + _strNewLine;
                    strCode += "                }" + _strNewLine;
                    strCode += "            }" + _strNewLine;

                    if (_entLibVersion == "5")
                    {
                        strCode += "            using (RefCountingDataReader refCountingDataReader = ((RefCountingDataReader)db.ExecuteReader(dbCommand)))" + _strNewLine;
                    }
                    else
                    {
                        strCode += "            using (SqlDataReader dataReader = (SqlDataReader)db.ExecuteReader(dbCommand))" + _strNewLine;
                    }


                    strCode += "            {" + _strNewLine;

                    if (_entLibVersion == "5")
                        strCode += "                SqlDataReader dataReader = (SqlDataReader)refCountingDataReader.InnerReader;" + _strNewLine;
                    strCode += "                DataTable dataTable = new DataTable();" + _strNewLine;
                    strCode += "                dataTable.Load(dataReader);" + _strNewLine;
                    strCode += "                return dataTable;" + _strNewLine;
                    strCode += "            }" + _strNewLine;
                    strCode += "        }" + _strNewLine;
                    strCode += "   " + _strNewLine;
                }
            }
        }

        protected static void AddAddInParameters(ref string strCode, DbTable dbTable)
        {
            strCode += "        /// <summary>" + _strNewLine;
            strCode += "        /// Adds all of the used In dbParameter objects to the given command from an IP object " + _strNewLine;
            strCode += "        /// </summary>" + _strNewLine;
            strCode += "        public static void AddInParameters(" + dbTable.ClassName + "IP " + dbTable.ObjectName + "IP, Database db, DbCommand dbCommand, string strInputParameterSuffix)" + _strNewLine;
            strCode += "        {" + _strNewLine;

            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + " != null)" + _strNewLine;
                strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "");

                // Add extra for datetime columns
                if (GetCodeTypeFromDbType(dbColumn.DataType) == "DateTime" || GetCodeTypeFromDbType(dbColumn.DataType) == "smalldatetime")
                {
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Date");

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Time");
                }

                // Add property LIKE '%input%', 'input%' and '%input'
                if (dbColumn.ShouldHaveLikeParameters())
                {
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like != null)" + _strNewLine;
                    strCode += "                db.AddInParameter(dbCommand, \"" + dbColumn.ColumnName + "_Like\" + strInputParameterSuffix, DbType." + GetEntLibTypeFromDbType(dbColumn.DataType) + ", \"%\" + " + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like + \"%\");";

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like_Start != null)" + _strNewLine;
                    strCode += "                db.AddInParameter(dbCommand, \"" + dbColumn.ColumnName + "_Like_Start\" + strInputParameterSuffix, DbType." + GetEntLibTypeFromDbType(dbColumn.DataType) + ", " + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like_Start + \"%\");";

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like_End != null)" + _strNewLine;
                    strCode += "                db.AddInParameter(dbCommand, \"" + dbColumn.ColumnName + "_Like_End\" + strInputParameterSuffix, DbType." + GetEntLibTypeFromDbType(dbColumn.DataType) + ", \"%\" + " + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Like_End);";

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Not != null)" + _strNewLine;
                    strCode += "                db.AddInParameter(dbCommand, \"" + dbColumn.ColumnName + "_Not\" + strInputParameterSuffix, DbType." + GetEntLibTypeFromDbType(dbColumn.DataType) + ", " + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Not);";
                }

                // Add property <= and =>
                if (dbColumn.ShouldHaveFromAndToParameters())
                {
                    // Obsolete
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_From != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_From");

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_To != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_To");

                    // Replaces old
                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_LesserOrEqualThan != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_LesserOrEqualThan");

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_GreaterOrEqualThan != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_GreaterOrEqualThan");

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_LesserThan != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_LesserThan");

                    strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_GreaterThan != null)" + _strNewLine;
                    strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_GreaterThan");

                    // Add extra for datetime columns
                    if (GetCodeTypeFromDbType(dbColumn.DataType) == "DateTime")
                    {
                        // Obsolete
                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_From != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Date_From");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_To != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Date_To");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_From != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Time_From");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_To != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Time_To");

                        // Replaces old
                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_LesserOrEqualThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Date_LesserOrEqualThan");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_GreaterOrEqualThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Date_GreaterOrEqualThan");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_LesserThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Date_LesserThan");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Date_GreaterThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Date_GreaterThan");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_LesserOrEqualThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Time_LesserOrEqualThan");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_GreaterOrEqualThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Time_GreaterOrEqualThan");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_LesserThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Time_LesserThan");

                        strCode += "            if(" + dbTable.ObjectName + "IP." + dbColumn.ColumnName + "_Time_GreaterThan != null)" + _strNewLine;
                        strCode += "    " + GetAddInParameter(dbTable, dbColumn, "", "_Time_GreaterThan");
                    }
                }

                strCode += "   " + _strNewLine;
            }

            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        private static string GetAddInParameter(DbTable dbTable, DbColumn dbColumn, string strPropertyPrefix, string strPropertySuffix)
        {
            return "            db.AddInParameter(dbCommand, \"" + strPropertyPrefix + dbColumn.ColumnName + strPropertySuffix + "\" + strInputParameterSuffix, DbType." + GetEntLibTypeFromDbType(dbColumn.DataType) + ", " + dbTable.ObjectName + "IP." + dbColumn.ColumnName + strPropertySuffix + ");" + _strNewLine;
        }

        protected static void AddInsertMethod(ref string strCode, DbTable dbTable)
        {
            strCode += "        internal static int Insert(" + dbTable.ClassName + "BO " + dbTable.ObjectName + ")" + _strNewLine;
            strCode += "        {" + _strNewLine;

            strCode += "            Database db = GetDatabase();" + _strNewLine;

            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(\"INSERT INTO " + dbTable.TableName + " (";

            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                if (!dbColumn.IsPK)
                {
                    strCode += dbColumn.ColumnName + ", ";
                }
            }
            strCode = strCode.Substring(0, strCode.Length - 2);
            strCode += ") VALUES (";

            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                if (!dbColumn.IsPK)
                {
                    strCode += "@" + dbColumn.ColumnName + ", ";
                }
            }

            strCode = strCode.Substring(0, strCode.Length - 2);
            strCode += "); SELECT SCOPE_IDENTITY();\");" + _strNewLine;
            AddInParameters(ref strCode, dbTable, false);
            strCode += "            " + dbTable.ObjectName + "." + dbTable.ClassName + "Id = int.Parse(db.ExecuteScalar(dbCommand).ToString());" + _strNewLine;
            strCode += "            return " + dbTable.ObjectName + "." + dbTable.ClassName + "Id;" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddInsertWithIdentityMethod(ref string strCode, DbTable dbTable)
        {
            strCode += "        internal static int Insert(" + dbTable.ClassName + "BO " + dbTable.ObjectName + ", bool blnSaveWithIdentity)" + _strNewLine;
            strCode += "        {" + _strNewLine;

            strCode += "            Database db = GetDatabase();" + _strNewLine;

            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(\"SET IDENTITY_INSERT " + dbTable.TableName + " ON;INSERT INTO " + dbTable.TableName + " (";

            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                strCode += dbColumn.ColumnName + ", ";
            }
            strCode = strCode.Substring(0, strCode.Length - 2);
            strCode += ") VALUES (";

            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                strCode += "@" + dbColumn.ColumnName + ", ";
            }

            strCode = strCode.Substring(0, strCode.Length - 2);
            strCode += "); SELECT SCOPE_IDENTITY();SET IDENTITY_INSERT " + dbTable.TableName + " OFF;\");" + _strNewLine;
            AddInParameters(ref strCode, dbTable, true);
            strCode += "            " + dbTable.ObjectName + "." + dbTable.ClassName + "Id = int.Parse(db.ExecuteScalar(dbCommand).ToString());" + _strNewLine;
            strCode += "            return " + dbTable.ObjectName + "." + dbTable.ClassName + "Id;" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddInParameters(ref string strCode, DbTable dbTable, bool blnAllowIdentity)
        {
            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                if (dbColumn.ColumnName == "Modified")
                    strCode += "            db.AddInParameter(dbCommand, \"Modified\", DbType.DateTime, DateTime.Now);";
                else if ((!dbColumn.IsPK || blnAllowIdentity))
                    strCode += "            db.AddInParameter(dbCommand, \"" + dbColumn.ColumnName + "\", DbType." + GetEntLibTypeFromDbType(dbColumn.DataType) + ", " + dbTable.ObjectName + "." + dbColumn.ColumnName + ");";
            }
        }

        protected static void AddDeleteMethod(ref string strCode, DbTable dbTable)
        {
            if(dbTable.DbColumns.Exists(column=>column.ColumnName=="Deleted"))
            {
                strCode += "        internal static void Delete(" + dbTable.ClassName + "BO " + dbTable.ObjectName + ")" + _strNewLine;
                strCode += "        {" + _strNewLine;
                strCode += "            Database db = GetDatabase();" + _strNewLine;
                strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(\"UPDATE " + dbTable.TableName + " SET Deleted=@Deleted WHERE " + dbTable.ClassName + "Id = @" + dbTable.ClassName + "Id\");" + _strNewLine;
                strCode += "            db.AddInParameter(dbCommand, \"" + dbTable.ClassName + "Id\", DbType.Int32, " + dbTable.ObjectName + "." + dbTable.ClassName + "Id);" + _strNewLine;
                strCode += "            db.AddInParameter(dbCommand, \"Deleted\", DbType.Boolean, true);" + _strNewLine;
                strCode += "            db.ExecuteNonQuery(dbCommand);" + _strNewLine;
                strCode += "        }" + _strNewLine;
                strCode += " " + _strNewLine;
            }
            strCode += "        internal static void DeleteFromDatabase(" + dbTable.ClassName + "BO " + dbTable.ObjectName + ")" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            Database db = GetDatabase();" + _strNewLine;
            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(\"DELETE FROM " + dbTable.TableName + " WHERE " + dbTable.ClassName + "Id = @" + dbTable.ClassName + "Id\");" + _strNewLine;
            strCode += "            db.AddInParameter(dbCommand, \"" + dbTable.ClassName + "Id\", DbType.Int32, " + dbTable.ObjectName + "." + dbTable.ClassName + "Id);" + _strNewLine;
            strCode += "            db.ExecuteNonQuery(dbCommand);" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddUpdateMethod(ref string strCode, DbTable dbTable)
        {
            strCode += "        internal static void Update(" + dbTable.ClassName + "BO " + dbTable.ObjectName + ")" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            Database db = GetDatabase();" + _strNewLine;

            strCode += "            DbCommand dbCommand = db.GetSqlStringCommand(\"UPDATE " + dbTable.TableName + " SET ";

            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                if (!dbColumn.IsPK && dbColumn.ColumnName != "Created")
                {
                    strCode += dbColumn.ColumnName + " = @" + dbColumn.ColumnName + ", ";
                }
            }
            strCode = strCode.Substring(0, strCode.Length - 2);
            strCode += " WHERE " + dbTable.ClassName + "Id = @" + dbTable.ClassName + "Id\");" + _strNewLine;
            strCode += "            db.AddInParameter(dbCommand, \"" + dbTable.ClassName + "Id\", DbType.Int32, " + dbTable.ObjectName + "." + dbTable.ClassName + "Id);" + _strNewLine;
            AddInParameters(ref strCode, dbTable, false);

            strCode += "            db.ExecuteNonQuery(dbCommand);" + _strNewLine;


            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static void AddGetBOFromDataRecord(ref string strCode, DbTable dbTable)
        {
            strCode += "        internal static " + dbTable.ClassName + "BO GetBOFromDataRecord(IDataRecord dataRecord)" + _strNewLine;
            strCode += "        {" + _strNewLine;
            strCode += "            " + dbTable.ClassName + "BO " + dbTable.ObjectName + " = new " + dbTable.ClassName + "BO();" + _strNewLine;
            foreach (DbColumn dbColumn in dbTable.DbColumns)
            {
                if (dbColumn.IsNullable)
                {
                    if (dbColumn.DataType == "binary" || dbColumn.DataType == "varbinary")
                    {
                        strCode += "            if(dataRecord.IsDBNull(dataRecord.GetOrdinal(\"" + dbColumn.ColumnName + "\")))" + _strNewLine;
                        strCode += "                " + dbTable.ObjectName + "." + dbColumn.ColumnName + " = null;" + _strNewLine;
                        strCode += "            else" + _strNewLine;
                        strCode += "                dataRecord.GetBytes(dataRecord.GetOrdinal(\"" + dbColumn.ColumnName + "\"), 0, " + dbTable.ObjectName + "." + dbColumn.ColumnName + ", 0, " + dbColumn.MaxLength + ");" + _strNewLine;
                    }
                    else
                    {
                        strCode += "            if(dataRecord.IsDBNull(dataRecord.GetOrdinal(\"" + dbColumn.ColumnName + "\")))" + _strNewLine;
                        strCode += "                " + dbTable.ObjectName + "." + dbColumn.ColumnName + " = null;" + _strNewLine;
                        strCode += "            else" + _strNewLine;
                        strCode += "                " + dbTable.ObjectName + "." + dbColumn.ColumnName + " = dataRecord.Get" + GetEntLibTypeFromDbType(dbColumn.DataType) + "(dataRecord.GetOrdinal(\"" + dbColumn.ColumnName + "\"));" + _strNewLine;
                    }
                }
                else
                {
                    if (dbColumn.DataType == "binary" || dbColumn.DataType == "varbinary")
                        strCode += "            dataRecord.GetBytes(dataRecord.GetOrdinal(\"" + dbColumn.ColumnName + "\"), 0, " + dbTable.ObjectName + "." + dbColumn.ColumnName + ", 0, " + dbColumn.MaxLength + ");" + _strNewLine;
                    else
                        strCode += "            " + dbTable.ObjectName + "." + dbColumn.ColumnName + " = dataRecord.Get" + GetEntLibTypeFromDbType(dbColumn.DataType) + "(dataRecord.GetOrdinal(\"" + dbColumn.ColumnName + "\"));" + _strNewLine;
                }
            }

            strCode += "            return " + dbTable.ObjectName + ";" + _strNewLine;
            strCode += "        }" + _strNewLine;
            strCode += "   " + _strNewLine;
        }

        protected static string GetEntLibTypeFromDbType(string strDbType)
        {
            switch (strDbType)
            {
                case "int":
                    return "Int32";
                case "uniqueidentifier":
                    return "Guid";
                case "datetime":
                    return "DateTime";
                case "smalldatetime":
                    return "DateTime";
                case "bit":
                    return "Boolean";
                case "nvarchar":
                    return "String";
                case "char":
                    return "String";
                case "binary":
                    return "Binary";
                case "xml":
                    return "Xml";
                case "ntext":
                    return "String";
                case "nchar":
                    return "String";
                case "bigint":
                    return "Int64";
                case "float":
                    return "Double";
                case "varbinary":
                    return "Byte";
                default:
                    throw new Exception("I dont recognize the type from the DB: " + strDbType);
            }
        }

    }
}
